package simorg.domain.persistence;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import simorg.domain.ClassificationTest;
import simorg.domain.Compound;
import simorg.domain.DerivativeTest;
import simorg.domain.ResultString;
import simorg.domain.Test;
import simorg.domain.TestCompoundResult;
import simorg.util.DatabaseConnection;

public class ExcelImport {

	public static final String IMPORT_FILE = "/Users/sfrees/dev/simorg/SimOrg.xls";
	
	public static String getAsString(HSSFCell cell) {
		if ( cell == null ) return null;
		 switch ( cell.getCellType() ) {
         case HSSFCell.CELL_TYPE_NUMERIC:
            return String.valueOf(cell.getNumericCellValue() );
         case HSSFCell.CELL_TYPE_STRING: 
        	 return String.valueOf( cell.getStringCellValue() );
         case HSSFCell.CELL_TYPE_FORMULA: 
        	 return String.valueOf( cell.getNumericCellValue() );
         default:
        	 return null;
     }
	}
	public static int doubleStringToInt(String doubleString){
		double m = Double.parseDouble(doubleString);
        return (int)Math.round(m);
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		DatabaseConnection dc = null;
		System.out.println("Importing from " + IMPORT_FILE);
		System.out.println("  This import will DROP COMPOUND TABLE -DELETING ALL COMPOUND DATA CURRENTLY IN THE TABLE");
		try {
			dc = new DatabaseConnection();
			InputStream input = new FileInputStream(new File(IMPORT_FILE));
	        POIFSFileSystem fs = new POIFSFileSystem( input );
	        HSSFWorkbook wb = new HSSFWorkbook(fs);
	        
	        
			CompoundTable.bootstrap(true);
			TestTable.bootstrap(true);
			ResultStringTable.bootstrap(true);
			TestCompoundResultTable.bootstrap(true);
			
			// Import Tests
			Map<Integer, Test> classTests = new HashMap<Integer, Test>();
			Map<Integer, Test> derivTests = new HashMap<Integer, Test>();
			// Import Classification tests first.s
			HSSFSheet sheet = wb.getSheet("Tests");
	        Iterator rows = sheet.rowIterator(); 
	        int num = 0;
            while( rows.hasNext() ) {           
                HSSFRow row = (HSSFRow) rows.next();
                String name = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
                num ++;
                if (name.equals("Test Name") || name.length() < 1 ) continue;
                
                String minutes = getAsString(row.getCell(2)) == null ? "" : getAsString(row.getCell(2));
                String description = row.getCell(3) == null ? "" : row.getCell(3).getStringCellValue();
                String order = getAsString(row.getCell(4)) == null ? "-1" : getAsString(row.getCell(4));
                String weighting = getAsString(row.getCell(5)) == null ? "-1" : getAsString(row.getCell(5));
                String weightingComment = row.getCell(6) == null ? "" : row.getCell(6).getStringCellValue();
                System.out.println("Loaded Classification Test:  " + name);
                Test test = new ClassificationTest();
                test.setId(num);
                test.setName(name);
                test.setMinutes(doubleStringToInt(minutes));
                test.setDescription(description);
                test.setOrder(doubleStringToInt(order));
                test.setWeight(doubleStringToInt(weighting));
                test.setWeightingComment(weightingComment);
                TestTable.save(dc, test);
                classTests.put(num, test);
                
            }
            
            sheet = wb.getSheet("Derivatives");
	        rows = sheet.rowIterator(); 
	        while( rows.hasNext() ) {           
                HSSFRow row = (HSSFRow) rows.next();
                String name = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
                num ++;
                if (name.equals("Derivative Name") || name.length() < 1 ) continue;
                
                String minutes = getAsString(row.getCell(2)) == null ? "" : getAsString(row.getCell(2));
                String description = row.getCell(3) == null ? "" : row.getCell(3).getStringCellValue();
                String order = getAsString(row.getCell(4)) == null ? "-1" : getAsString(row.getCell(4));
                String weighting = getAsString(row.getCell(5)) == null ? "-1" : getAsString(row.getCell(5));
                String weightingComment = row.getCell(6) == null ? "" : row.getCell(6).getStringCellValue();
                System.out.println("Loaded Derivative Test:  " + name);
                Test test = new DerivativeTest();
                test.setId(num);
                test.setName(name);
                test.setMinutes(doubleStringToInt(minutes));
                test.setDescription(description);
                test.setOrder(doubleStringToInt(order));
                test.setWeight(doubleStringToInt(weighting));
                test.setWeightingComment(weightingComment);
                TestTable.save(dc, test);
                derivTests.put(num, test);
            }
			
	        ResultString rs = new ResultString();
            rs.setId(1);
            rs.setText("The result was negative");
            ResultStringTable.save(dc, rs);
            
	        sheet = wb.getSheet("Strings");
	        rows = sheet.rowIterator(); 
	        while( rows.hasNext() ) {           
                HSSFRow row = (HSSFRow) rows.next();
                String id =  getAsString(row.getCell(0)) == null ? "-1" : getAsString(row.getCell(0));
                if ( id == null || id.equals("Num")  || doubleStringToInt(id) == -1 ) continue;
                
                String text =  row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
                rs = new ResultString();
                rs.setId(doubleStringToInt(id));
                rs.setText(text);
                System.out.println(id + ":  " + text);
                ResultStringTable.save(dc, rs);
            }
			
			
			// Import Compounds
			Map<String, Compound> compounds = new HashMap<String, Compound>();
	        sheet = wb.getSheetAt(0);
	        rows = sheet.rowIterator();
	        num = 0;
            while( rows.hasNext() ) {           
                HSSFRow row = (HSSFRow) rows.next();
                String name = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
                String className = row.getCell(3) == null ? "" : row.getCell(3).getStringCellValue();
                String meltingPoint = getAsString(row.getCell(4)) == null ? "-1" : getAsString(row.getCell(4));
                String boilingPoint = getAsString(row.getCell(5)) == null ? "-1" : getAsString(row.getCell(5));
                if ( name.equals("CommonName") || name.length() < 1) continue;
                
                num++;
                 System.out.println(row.getRowNum() + ":  " + name + " > " + className); 
                Compound c = new Compound();
                c.setId(num);
                c.setLabel(name);
                c.setClassName(className);
                c.setBoilingPoint(Float.parseFloat(boilingPoint));
                c.setMeltingPoint(Float.parseFloat(meltingPoint));
                CompoundTable.save(dc, c);
                
                int testID = 2;
                int baseRow = 5;
                for (int i = testID; i <= 46; i++ )  {
                	String testResult = row.getCell(baseRow + i) == null ? "" : getAsString(row.getCell(baseRow + i));
                	
                	if ( testResult != null && testResult.trim().length() > 0 ) {
                		if ( testResult.trim().contains(".") ) 
                				testResult = testResult.trim().substring(0, testResult.indexOf('.'));
                		
                		if ( testResult.trim().endsWith("d") ) {
                			testResult = testResult.trim().substring(0, testResult.trim().length()-1);
                		}
                		if ( testResult.trim().endsWith("d.") ) {
                			testResult = testResult.trim().substring(0, testResult.trim().length()-2);
                		}
                		System.out.println(testResult);
                		TestCompoundResult tcr = new TestCompoundResult();
                		tcr.setCompoundId(num);
                		tcr.setTestId(i);
                		tcr.setPrimaryResultString(Integer.parseInt(testResult));
                		tcr.setSecondaryResultString(Integer.parseInt(testResult));
                		TestCompoundResultTable.save(dc, tcr);
                	}
                }
                
            }
            
            
            
            
		}
        catch (Exception e ) {
        	e.printStackTrace();
        }
        finally {
			if ( dc != null ) dc.close();
		}
	}

}
